package io.renren.modules.sys.util;

import io.renren.modules.sys.util.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by dell on 2018/10/17.
 */
public class ReadExcelUtils {

    public static List<String[]> readfile(MultipartFile files, Integer columnnumnum) throws Exception {

        List<String[]> list = new ArrayList<String[]>();
        String fileName = files.getOriginalFilename();
        String prefix = fileName.substring(fileName.lastIndexOf("."));
        try {
            final File excelFile = File.createTempFile(DateUtil.getCurrentDateStr("yyyyMMddhhmmss"), prefix);
            // MultipartFile to File
            files.transferTo(excelFile);

            InputStream inputStream = new FileInputStream(excelFile);
            if (prefix.equals(".xls")) {
                HSSFWorkbook res = new HSSFWorkbook(inputStream);
                HSSFSheet hssfSheet = res.getSheetAt(0);
                HSSFCell cell = null;
                for (int rowNum = 4; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    String[] sarr = null;
                    sarr = new String[columnnumnum];
                    Boolean sw = true;
                    for (int i = 0; i < columnnumnum; i++) {
                        cell = hssfRow.getCell(i + 1);
                        if (cell!=null){
                            Object o = getCellValue(cell);
                            if (o.toString().length() > 0) {
                                sarr[i] = getCellValue(cell).toString();
                            }

                        }


                    }

                    boolean isok=true;
                    if (sarr.length==3){
                        if (sarr[0]==null &&sarr[1]==null &&sarr[2]==null){
                            isok=false;
                        }
                    }

                    if (isok){
                        list.add(sarr);
                    }else {
                        break;
                    }
                }
            }
            if (prefix.equals(".xlsx")) {
                XSSFWorkbook res = new XSSFWorkbook(inputStream);
                XSSFSheet hssfSheet = res.getSheetAt(0);
                XSSFCell xssfCell = null;
                for (int rowNum = 4; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    XSSFRow xssfRow = hssfSheet.getRow(rowNum);
                    String[] sarr = null;
                    sarr = new String[columnnumnum];
                    Boolean sw = true;
                    for (int i = 0; i < columnnumnum; i++) {
                        xssfCell = xssfRow.getCell(i + 1);
                        if (xssfCell!=null){
                            Object o = getCellValue(xssfCell);
                            if (o.toString().length() > 0) {
                                sarr[i] = getCellValue(xssfCell).toString();
                            }
                        }


                    }
                    boolean isok=true;
                    if (sarr.length==3){
                        if (sarr[0]==null &&sarr[1]==null &&sarr[2]==null){
                            isok=false;
                        }
                    }

                    if (isok){
                        list.add(sarr);
                    }else {
                        break;
                    }

                }
            }
            inputStream.close();
            excelFile.delete();
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 任务列表导入
     * @param files 文件
     * @return list集合
     * @throws Exception
     */
    public static List<String> readTaskfile(MultipartFile files) throws Exception {

        List<String> list = new ArrayList<String>();
        String fileName = files.getOriginalFilename();
        String prefix = fileName.substring(fileName.lastIndexOf("."));
        File excelFile = null;
        try {
//            final File excelFile = File.createTempFile(DateUtil.getCurrentDateStr("yyyyMMddhhmmss"), prefix);
            excelFile  = File.createTempFile(DateUtil.getCurrentDateStr("yyyyMMddhhmmss"), prefix);
            // MultipartFile to File
            files.transferTo(excelFile);
            InputStream inputStream = new FileInputStream(excelFile);
            if (prefix.equals(".xls")) {
                HSSFWorkbook res = new HSSFWorkbook(inputStream);
                HSSFSheet hssfSheet = res.getSheetAt(0);
                HSSFCell cell = null;
                for (int rowNum = 4; rowNum <= (hssfSheet.getLastRowNum()-1); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    String sarr = "";
                    if(hssfRow!=null){
                        cell = hssfRow.getCell(1);
                        if(cell==null){
                            continue;
                        }
                        Object o = getCellValue(cell);
                        if (o.toString().length() > 0) {
                            sarr = getCellValue(cell).toString();
                        }
                        list.add(sarr);
                    }else{
                        continue;
                    }

                }
            }
            if (prefix.equals(".xlsx")) {
                XSSFWorkbook res = new XSSFWorkbook(inputStream);
                XSSFSheet    hssfSheet = res.getSheetAt(0);
                XSSFCell xssfCell = null;
                for (int rowNum = 4; rowNum <= (hssfSheet.getLastRowNum()-1); rowNum++) {
                    XSSFRow xssfRow = hssfSheet.getRow(rowNum);
                    String sarr = "";
                    if(xssfRow!=null){
                        xssfCell = xssfRow.getCell(1);
                        if(xssfCell==null){
                            continue;
                        }
                        Object o = getCellValue(xssfCell);
                        if (o.toString().length() > 0) {
                            sarr = getCellValue(xssfCell).toString();
                        }

                        list.add(sarr);
                    }else{
                        continue;
                    }

                }
            }
            inputStream.close();
            excelFile.delete();
            return list;
        } catch (Exception e) {
            e.printStackTrace();

            return null;

        }


    }



    public static Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy/MM/dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

        switch (cell.getCellType()) {
//            case Cell.CELL_TYPE_STRING:
//                value = cell.getRichStringCellValue().getString();
//                break;
//            case Cell.CELL_TYPE_NUMERIC:
//                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                    value = df.format(cell.getNumericCellValue());
//                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
//                    value = sdf.format(cell.getDateCellValue());
//                } else {
//                    value = df2.format(cell.getNumericCellValue());
//                }
//                break;
//            case Cell.CELL_TYPE_BOOLEAN:
//                value = cell.getBooleanCellValue();
//                break;
//            case Cell.CELL_TYPE_BLANK:
//                value = "";
//                break;
//            default:
//                break;
        }
        return value;
    }

//    public static void main(String[] args) throws FileNotFoundException {
//        List<String[]> list = new ArrayList<String[]>();
//        File file = new File("C:\\Users\\dell\\Desktop\\数据中心\\导入表1019\\导入表1019\\test.xlsx");
//        InputStream inputStream = new FileInputStream(file);
//        XSSFWorkbook res = null;
//        try {
//            res = new XSSFWorkbook(inputStream);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//        XSSFSheet hssfSheet = res.getSheetAt(0);
//        XSSFCell xssfCell = null;
//        for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
//            XSSFRow xssfRow = hssfSheet.getRow(rowNum);
//            String[] sarr = null;
//            sarr = new String[14];
//            Boolean sw = true;
//            for (int i = 0; i < 14; i++) {
//                xssfCell = xssfRow.getCell(i + 1);
//                Object o = getCellValue(xssfCell);
//                System.out.println(o.toString().length() > 0);
//                if (o.toString().length() > 0) {
//                    sarr[i] = getCellValue(xssfCell).toString();
//                } else {
//
//                    sw = false;
//                    break;
//                }
//
//            }
//            if (sw) {
//
//
//                list.add(sarr);
//            }
//        }
//
//
//        System.out.println(JSON.toJSONString(list));
//    }
}
